﻿using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using WPFExcelReport.Helpers;
using System.Data.OracleClient;
using System.IO;
using INTEX.DataBase.Tools;
using INTEX.DataBase;
using System.Linq;
namespace WPFExcelReport
{
    public partial class ITXEXRPT
    {
        SETINTEXDB INTEXDB = new SETINTEXDB();
        private void YCAEK03EXT(DataSet db)
        {
            try
            {
                EKKWE1 = 0;
                EKKME1 = 0;
                EKKAPPLY = 0;
                EKKPAIED = 0;
                if (!db.Tables.Contains("EKKOPF"))
                    return;
                DataRow dwEKK = db.Tables["EKKOPF"].Rows[0];
                string value = "";
                Dictionary<string, int> colorpos = new Dictionary<string, int>();
                int x = 1, y = 1;

                x = 5; y = 18;
                //exAgent.SetCellsPicture(5, 17, @"D:\DS40\A1.jpg"); 
                value = dwEKK["EKKAUFKRS"].ToString() + "-" + dwEKK["EKKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);

                x = 3; y = 30;
                exAgent.SetCellValue(x, y, date);
                FillInEXHead(dwEKK);
                int rown = FillInEKPOSEX();
                exAgent.SetCellValue(11, 24, EKKAPPLY);
                exAgent.SetCellValue(11, 32, EKKPAIED);
                exAgent.SetCellValue(11, 15, EKKWE1);
                FillinSing(db, rown);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void Payment(DataRow dwEKPOS, ref int rown)
        {
            string 尾款标志 = dwEKPOS["EKPLSCOL"].ToString().Trim();
            decimal EKPapplied = 0;
            string paiedhistory = "";
            string apphistory = "";
            decimal EKPpaied = 0;
            decimal lastappnum = 0;// EKVABLAGE,EKVBELEG
            string ordernum = Helpers.Helper.Left(dwEKPOS["EKPAUFKRS"].ToString() + "-" + dwEKPOS["EKPAUFLFD"].ToString() + "-" + dwEKPOS["EKPAUFPOS"].ToString() + "                       ", 23);
            var queryx = from DataRow row in dsSource.Tables["EKVRECH"].AsEnumerable()
                         where row["EKVFILLER"].ToString() == ordernum
                         select row;
            foreach (DataRow row in queryx)
            {
                decimal appnum = Convert.ToDecimal(row["EKVABLAGE"].ToString() + row["EKVBELEG"].ToString());

                if (row["EKVKIR"].ToString() == "K")
                {
                    decimal pay = System.Convert.ToDecimal(row["EKVBETRAG"].ToString()) / 100;
                    EKPpaied += pay;
                    EKKPAIED += pay;
                    paiedhistory += pay + ",";
                }
                if (row["EKVKIR"].ToString() == " ")
                {
                    decimal app = System.Convert.ToDecimal(row["EKVBETRAG"].ToString()) / 100;
                    //EKPapplied += app;
                    if (appnum > lastappnum)
                    {
                        EKPapplied = app;
                        lastappnum = appnum;
                    }
                    EKKAPPLY += app;
                    apphistory += app + ",";
                }
            }
            paiedhistory += "***";
            paiedhistory = paiedhistory.Replace(",***", "");
            exAgent.SetCellValue(rown + 1, 16, "请款记录");
            if (尾款标志 == "")
                尾款标志 = "N";
            exAgent.SetCellValue(rown + 1, 20, "本次请款("+尾款标志+")：");
            exAgent.SetCellValue(rown + 1, 25, EKPapplied);
            //exAgent.SetCellValue(rown + 2, 20, "请款：");
            exAgent.SetCellValue(rown + 1, 28, apphistory);
            exAgent.SetCellValue(rown + 2, 20, "已付：");
            exAgent.SetCellValue(rown + 2, 25, EKPpaied);
            exAgent.SetCellValue(rown + 2, 28, paiedhistory);
        }
        private int FillInEKPOSEX()
        {
            int rown = 21;
            int idx = 0;
            int Count = 0;
            string value = "";
            string artdes = "", colordes = "";
            var qek = from DataRow rek in dsSource.Tables["ICC_EKPOS"].AsEnumerable()
                      orderby rek["EKPAUFPOS"].ToString() ascending
                      select rek;
            Count = qek.Count();
            double LineTotal = 0;
            foreach (DataRow dwEKPOS in qek)
            {

                LineTotal = FillinAddEx(dwEKPOS, ref rown);//Surcharge/Dicount

                if (IsApplyPayment)
                {
                    Payment(dwEKPOS, ref rown);
                }
                #region=======EKPOS=========
                DataRow dwArticle = null;
                DataRow dwColor = null;
                string exart = "";
                int pos = System.Convert.ToInt16(dwEKPOS["EKPAUFPOS"].ToString());
                string artg = dwEKPOS["EKPARTGR"].ToString();
                string art = dwEKPOS["EKPART"].ToString();
                string var = dwEKPOS["EKPVAR"].ToString();
                var = Left(var, 2);
                string structure = "";
                string composition = "";
                string excolor = "";

               
                string color = dwEKPOS["EKPFARBE"].ToString();
                string size = dwEKPOS["EKPGROE"].ToString();
                string cref = dwEKPOS["EKPLSGRPBEZ"].ToString().Trim();
                string um = dwEKPOS["EKPMEEH"].ToString();
                double qty = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPME1"].ToString()) * 0.01, 2);
                double tvalue = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPWE1"].ToString()) * 0.01, 2);
                double price = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPPRME"].ToString()) * 0.001, 2);
                double gsqm = 1;
                double width = 1;
                exAgent.SetCellValue(11, 10, um);
                value = dwEKPOS["EKPLTERM"].ToString();
                exAgent.SetCellValue(4, 30, value);
                var query = from rows in dsSource.Tables["FSSTAMM"].AsEnumerable()
                            where rows["FSART"].ToString() == art
                            select rows;
                switch (artg)
                {
                    case "1":
                        break;
                    case "2":
                        break;
                    case "3":
                        query = from rows in dsSource.Tables["WASTAMM"].AsEnumerable()
                                where rows["WAART"].ToString() == art
                                & rows["WAVAR"].ToString() == var
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwArticle = rw;
                            artdes = GetGB2312Value(rw["WABEZ"].ToString());
                            exart = GetGB2312Value(dwArticle["WALOHNA"].ToString().Trim());
                            width = System.Math.Round(System.Convert.ToDouble(dwArticle["WAFBRTL"].ToString()) * 0.1, 0);
                            gsqm = System.Math.Round(System.Convert.ToDouble(dwArticle["WAFGRQM"].ToString()), 0);
                            structure = //System.Math.Round(System.Convert.ToDouble(dwArticle["WAFFDDIK"].ToString()) * 0.01, 0)
                                //+ "X" + System.Math.Round(System.Convert.ToDouble(dwArticle["WAFFDDIS"].ToString()) * 0.01, 0)
                             "幅宽:" + width + "cm"
                            + "," + gsqm + "g/sqm"
                            ;

                        }
                        composition = Helper.GetComposition(art, dsSource.Tables["STUELI"]);
                        query = from rows in dsSource.Tables["WAFARBEN"].AsEnumerable()
                                where rows["WAFART"].ToString() == art
                                & rows["WAFVAR"].ToString() == var
                               & rows["WAFZUSATZ"].ToString() == color
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwColor = rw;
                            colordes = GetGB2312Value(rw["WAFBEZ"].ToString());
                            excolor = GetGB2312Value(rw["WAFMUDESS"].ToString());
                        }
                        break;

                    case "4":
                        break;
                    case "5":
                        query = from rows in dsSource.Tables["FSSTAMM"].AsEnumerable()
                                where rows["FSART"].ToString() == art
                                & rows["FSVAR"].ToString() == var
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwArticle = rw;
                            artdes = GetGB2312Value(rw["FSABEZ"].ToString());
                        }
                        query = from rows in dsSource.Tables["FSFARBEN"].AsEnumerable()
                                where rows["FSFART"].ToString() == art
                                & rows["FSFVAR"].ToString() == var
                               & rows["FSFFARBE"].ToString() == color
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwColor = rw;
                            colordes = GetGB2312Value(rw["FSFFBEZ"].ToString());
                        }
                        break;
                    case "6":
                        break;
                    case "7":
                        break;
                    case "8":
                        query = from rows in dsSource.Tables["MSSTAMM"].AsEnumerable()
                                where rows["MSMAT"].ToString() == Left(art, 14)
                                & rows["MSVAR"].ToString() == var
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwArticle = rw;
                            artdes = GetGB2312Value(rw["MSBEZ"].ToString());
                        }
                        break;
                    default:
                        break;
                }
                exAgent.SetCellValue(rown, 1, pos);
                exAgent.SetCellValue(rown, 4, art.Trim() + var.Trim() + "(" + artdes.Trim() + ")");
                exAgent.SetCellValue(rown, 11, color.Trim() + "(" + colordes.Trim() + cref + ")");
                exAgent.SetCellValue(rown, 14, composition);
                exAgent.SetCellValue(rown, 16, qty);
                exAgent.SetCellValue(rown, 19, um);
                exAgent.SetCellValue(rown, 21, price);
                exAgent.SetCellValue(rown, 32, LineTotal);

                exAgent.SetCellValue(rown + 1, 1, "贵司品种");
                exAgent.SetCellValue(rown + 1, 4, exart);
                exAgent.SetCellValue(rown + 1, 11, excolor);

                exAgent.SetCellValue(rown + 2, 1, "幅宽");
                exAgent.SetCellValue(rown + 2, 4, width + "cm");
                exAgent.SetCellValue(rown + 2, 8, "克重");
                exAgent.SetCellValue(rown + 2, 12, gsqm + "g/sqm");
                //x = rown; y = 22;
                //exAgent.SetCellValue(x, y, size);  
                if (size.Trim() != "")
                {
                    exAgent.SetCellValue(20, 14, "尺码");
                    exAgent.SetCellValue(rown, 14, size);
                }
                #endregion
                FIllInREQMatrix(dwEKPOS, ref rown, width, gsqm);
               
                idx++;
                if (idx < Count)
                {
                    exAgent.RangeCopy(21, 25, rown, rown + 4);
                }
            }
            return rown;
        }
        private void FIllInREQMatrix(DataRow dwEKP, ref int rown, double width, double gsqm)
        {
            rown = rown + 3;
            exAgent.SetCellValue(rown, 1, "成衣款号");
            exAgent.SetCellValue(rown, 4, "生产单号");
            exAgent.SetCellValue(rown, 6, "需求数");
            exAgent.SetCellValue(rown, 8, "单耗");
            exAgent.SetCellValue(rown, 10, "需求量");
            exAgent.SetCellValue(rown, 12, "损耗率");
            exAgent.SetCellValue(rown, 14, "加成率");
            exAgent.SetCellValue(rown, 16, "应采量");
            exAgent.SetCellValue(rown, 18, "实采量");
            exAgent.SetCellValue(rown, 20, "实采推算数");
            exAgent.SetCellValue(rown, 23, "系统推算数");
            exAgent.SetCellValue(rown, 25, "收货地址/收货联系人");
            rown = rown + 1;
            int idx = 0;
            var qAB = from DataRow row in dsSource.Tables["ABMATRIX"].AsEnumerable()
                      where row["EKPAUFKRS"].ToString() == dwEKP["EKPAUFKRS"].ToString()
                      && row["EKPAUFLFD"].ToString() == dwEKP["EKPAUFLFD"].ToString()
                      && row["EKPAUFPOS"].ToString() == dwEKP["EKPAUFPOS"].ToString()
                      select row;
            int Count = qAB.Count();
            double WidthRatio = 1;
            double EKNQTY = 0;
            double EKPME1 = 0;
            double REQ = 0;
            string UM = dwEKP["EKPMEEH"].ToString();
            //double ratio = 0;
            foreach (DataRow row in qAB)
            {
                double reqqty = Convert.ToDouble(row["REQ"].ToString());
                REQ += reqqty;
            }
            foreach (DataRow row in qAB)
            {
                double TQTYD = Convert.ToDouble(row["TQTYD"].ToString());
                double bom = Convert.ToDouble(row["RATIO"].ToString());
                double BQty = TQTYD * bom;//根据计划数量推算出的需求量
                double planrate = 0;//加成比例
                double ABAQTYRESERVE = Convert.ToDouble(row["ABAQTYRESERVE"].ToString());
                string[] cost = row["EKPEART"].ToString().Split(new Char[] { ';' });
                double wastageek = 0;
                if (cost.Length > 1)
                {
                    WidthRatio = System.Convert.ToDouble(cost[1]);

                }
                else
                {
                    WidthRatio = 1;
                }
                if (cost.Length > 2)
                {
                    string str = cost[2];
                    if (str.Trim() == "")
                        str = "0";
                    planrate = System.Convert.ToDouble(str);
                }
                else
                {
                    planrate = 0;
                }
                wastageek = Math.Round(1 / WidthRatio - 1, 2);
                EKNQTY = System.Convert.ToDouble(row["EKPEFARBE"].ToString());
                EKPME1 = System.Convert.ToDouble(row["EKPME1"].ToString());
                double FGRLFD = System.Convert.ToDouble(row["FGRLFD"].ToString()) / 1000;
                if (UM.Trim() == "KG")
                {
                    FGRLFD = FGRLFD;
                }
                else
                {
                    FGRLFD = 1;
                }
                //ratio =Math.Round(((EKPME1 / EKNQTY) - 1),2);
                //double EKNQTY = Math.Round((EKPME1) * (WidthRatio), 2);
                exAgent.SetCellValue(rown + idx, 1, row["ABPART"].ToString());//"成衣款号");
                exAgent.SetCellValue(rown + idx, 4, Helper.GetSubRollNum(row["LFD"].ToString()));// "生产单号");
                exAgent.SetCellValue(rown + idx, 6, TQTYD);// "需求数");

                exAgent.SetCellValue(rown + idx, 8, bom);// "单耗");
                exAgent.SetCellValue(rown + idx, 10, Math.Round(BQty * FGRLFD, 1));//"需求量");
                exAgent.SetCellValue(rown + idx, 12, wastageek * 100);//"损耗率");
                exAgent.SetCellValue(rown + idx, 14, planrate * 100);//"加成率");
                exAgent.SetCellValue(rown + idx, 16, Math.Round(FGRLFD * TQTYD * bom * (1 + planrate) * (1 + wastageek), 2));// "应采量");根据初始下单量
                //exAgent.SetCellValue(rown + idx, 18, Math.Round(ABAQTYRESERVE * (1 + wastageek) * FGRLFD, 1));// "实采量");
                exAgent.SetCellValue(rown + idx, 18, Math.Round(ABAQTYRESERVE * FGRLFD, 1));// "实采量");
                exAgent.SetCellValue(rown + idx, 20, Math.Round((ABAQTYRESERVE / (1 + wastageek)) / bom, 0));// "实采推算数");
                exAgent.SetCellValue(rown + idx, 23, row["PQTY"].ToString());// "系统推算数");
                //exAgent.SetCellValue(xStart + idx * rown, 25, row["ABPART"].ToString());// "收货地址/收货联系人"); 
                string delstr = GetGB2312Value(row["LSSTRASSE1"].ToString().Trim() + ":" + row["LSAPNA1"].ToString().Trim()
                       + "(" + row["LSAPTEL1"].ToString().Trim() + ")");
                exAgent.SetCellValue(rown + idx, 25, delstr);// "收货地址/收货联系人"); 
                /*
                    rek["EKPME1"] = Math.Round(Convert.ToDouble(vw["REQ"].ToString()) * (1 + prodplanrate * 0.01) * (1 + wastageek * 0.01), 2);
                    rek["EKPEFARBE"] = Math.Round(Convert.ToDouble(rek["EKPME1"].ToString())/(1+wastageek*0.01),2);
                    rek["EKPEART"] = "0;" + (Math.Round(1 / (1 + wastageek*0.01),2)).ToString() + ";";
                 */
                idx++;
                if (idx < Count)
                {
                    exAgent.RangeCopy(25, 25, rown + idx, rown + idx);
                }
            }
            rown = rown + Count;
        }
        private void FillInEXHead(DataRow row)
        {
            string term = "";
            decimal rate = 0;
            decimal wert = 0;
            int d = 0;
            int x;
            decimal tqty = System.Convert.ToDecimal(row["EKKME1"].ToString()) / 100;
            exAgent.SetCellValue(11, 5, tqty);
            //decimal tvalue = System.Convert.ToDecimal(row["EKKWE1"].ToString()) / 100;

            //                EKPZUAB1	EKPZUABPR1	EKPZUABWE1                
            for (int j = 1; j < 6; j++)
            {
                term = row["EKKZUAB" + j].ToString();
                rate = System.Convert.ToDecimal(row["EKKZUABPR" + j].ToString()) / 100;
                wert = System.Convert.ToDecimal(row["EKKZUABWE" + j].ToString()) / 1000;
                if (term.Trim() != "")
                {
                    d++;
                    term = Helper.GetSDDAT(dsSource, "0255", term, "V");
                    x = 11 + d;
                    exAgent.SetCellValue(x, 4, GetGB2312Value(term));
                    if (rate != 0)
                    {
                        exAgent.SetCellValue(x, 24, rate + "%");
                    }
                    if (wert != 0)
                    {
                        exAgent.SetCellValue(x, 27, wert);
                    }
                }
            }
        }
        private double FillinAddEx(DataRow dwEKPOS, ref int rown)
        {
            //exAgent.SetCellValue(x, 27, "附加费:" + System.Convert.ToDecimal(dwEKPOS["EKPZUABWERT"].ToString()) / 100);
            string term = "";
            double rate = 0;
            double add = 0;
            double wert = 0;
            double discount = 0;
            double sur = 0;
            string addtext = "";
            string distext = "";
            int d = 0;
            int a = 0;

            double qty = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPME1"].ToString()) * 0.01, 2);
            double tvalue = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPWE1"].ToString()) * 0.01, 2);
            double price = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPPRME"].ToString()) * 0.001, 5);
            for (int j = 1; j < 6; j++)
            {
                term = dwEKPOS["EKPZUAB" + j].ToString();
                rate = System.Convert.ToDouble(dwEKPOS["EKPZUABPR" + j].ToString()) / 100;

                wert = System.Convert.ToDouble(dwEKPOS["EKPZUABWE" + j].ToString()) / 1000;

                if (term.Trim() != "")
                {
                    //d++;
                    term = Helper.GetSDDAT(dsSource, "0255", term, "V");
                    if (wert > 0)
                    {
                        add += wert;
                        addtext += term + ":" + wert;
                    }
                    else
                    {
                        discount += wert;
                        distext += term + ":" + wert;
                    }
                    //exAgent.SetCellValue(x, 4, GetGB2312Value(term));
                    if (rate != 0)
                    {
                        //exAgent.SetCellValue(x, 24, rate + "%");
                    }
                    if (wert != 0)
                    {
                        //exAgent.SetCellValue(x, 32, term+wert);
                    }
                    sur += wert;
                    //exAgent.RangeCopy(23, 23, x + 1, x + 1);
                }
            }
            tvalue = price * qty + sur;
            exAgent.SetCellValue(rown, 23, addtext);
            exAgent.SetCellValue(rown, 27, distext);
            EKKWE1 += Convert.ToDecimal(tvalue);
            return tvalue;
            //rown = rown + d;
            //EKKWE1 +=Convert.ToDecimal(tvalue);
            //EKKME1 += Convert.ToDecimal(qty);
        }

    }
}